"""  
       @File     : 空气质量.py
       @IDE      : PyCharm
       @Author   : 陈引弟
       @Date     : 2025/2/21 23:22
       @Desc     : 
=========================================================   
"""
import pandas as pd
import pymysql

# 读取 CSV 文件
csv_file = r'F:\python\基于spark的天气数据分析预测系统 - 副本\data_cleaning\不同城市的不同月份的平均空气质量指数.csv'
df = pd.read_csv(csv_file)

# 建立 MySQL 连接
try:
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='1234567',
        database='tianqi'
    )
    cursor = connection.cursor()

    # 插入数据到 wind_info 表（假设你要插入到这个表）
    for index, row in df.iterrows():
        insert_query = "INSERT INTO avg_air (city, weather_date, averageAir) VALUES (%s, %s, %s)"
        values = (row['city'], row['weather_date'], row['averageAir'])
        cursor.execute(insert_query, values)

    # 提交更改
    connection.commit()
    print("数据插入成功！")

except pymysql.Error as error:
    print(f"数据插入失败: {error}")

finally:
    try:
        cursor.close()
        connection.close()
        print("MySQL 连接已关闭。")
    except:
        pass